using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;
using System.Configuration;
namespace DAL
{
   public class tbSanPhamDAL
    {
        private SqlConnection cn = null;
        public tbSanPhamDAL()
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
            cn.Open();
        }

        public tbSanPhamDAL(tbSanPhamDAL tan)
        {
            this.cn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
            this.cn.Open();
        }

        public void Insert(tbSanPham th)
        {
            SqlCommand cm = new SqlCommand("tbSanPham_Insert", cn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@tinvantat", th.tinvantat);
            cm.Parameters.AddWithValue("@tensp", th.tensp);
            cm.Parameters.AddWithValue("@danhmuc", th.danhmuc);
            cm.Parameters.AddWithValue("@doanhnghiep", th.doanhnghiep);
            cm.Parameters.AddWithValue("@giagoc", th.giagoc);
            cm.Parameters.AddWithValue("@giaban", th.giaban);
            cm.Parameters.AddWithValue("@soluongban", th.soluongban);
            cm.Parameters.AddWithValue("@soluongtoida", th.soluongtoida);
            cm.Parameters.AddWithValue("@damua", th.damua);
            cm.Parameters.AddWithValue("@chietkhau", th.chietkhau);
            cm.Parameters.AddWithValue("@ngaysudung", th.ngaysudung);
            cm.Parameters.AddWithValue("@ngayhethan", th.ngayhethan);
            cm.Parameters.AddWithValue("@mota", th.mota);
            cm.Parameters.AddWithValue("@tinhtrang", th.tinhtrang);
            cm.Parameters.AddWithValue("@quydinh", th.quydinh);
            cm.Parameters.AddWithValue("@thongtinchitiet", th.thongtinchitiet);
            cm.Parameters.AddWithValue("@hinhchinh", th.hinhchinh);
            cm.ExecuteNonQuery();
        }
        public void Update(tbSanPham th)
        {
            SqlCommand cm = new SqlCommand("tbSanPham_Update", cn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@masp", th.masp);
            cm.Parameters.AddWithValue("@tinvantat", th.tinvantat);
            cm.Parameters.AddWithValue("@tensp", th.tensp);
            cm.Parameters.AddWithValue("@danhmuc", th.danhmuc);
            cm.Parameters.AddWithValue("@doanhnghiep", th.doanhnghiep);
            cm.Parameters.AddWithValue("@giagoc", th.giagoc);
            cm.Parameters.AddWithValue("@giaban", th.giaban);
            cm.Parameters.AddWithValue("@soluongban", th.soluongban);
            cm.Parameters.AddWithValue("@soluongtoida", th.soluongtoida);
            cm.Parameters.AddWithValue("@damua", th.damua);
            cm.Parameters.AddWithValue("@chietkhau", th.chietkhau);
            cm.Parameters.AddWithValue("@ngaysudung", th.ngaysudung);
            cm.Parameters.AddWithValue("@ngayhethan", th.ngayhethan);
            cm.Parameters.AddWithValue("@mota", th.mota);
            cm.Parameters.AddWithValue("@tinhtrang", th.tinhtrang);
            cm.Parameters.AddWithValue("@quydinh", th.quydinh);
            cm.Parameters.AddWithValue("@thongtinchitiet", th.thongtinchitiet);
            cm.Parameters.AddWithValue("@hinhchinh", th.hinhchinh);
            cm.ExecuteNonQuery();
        }

       public void UpdateTT(tbSanPham th)
       {
           SqlCommand cm = new SqlCommand("UpdateTinhTrangSP", cn);
           cm.CommandType = CommandType.StoredProcedure;
           cm.Parameters.AddWithValue("@masp", th.masp );
           cm.Parameters.AddWithValue("@tinhtrang", th.tinhtrang);
           cm.ExecuteNonQuery();
       }
        public void Delete(int iD)
        {
            SqlCommand cm = new SqlCommand("tbSanPham_Delete", cn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@masp", iD);
            cm.ExecuteNonQuery();
        }
       public tbSanPhamCollection Get(string where, int pageIndex, int pageSize,
           string orderBy, string orderDirection, out int totalRecords)
       {
           SqlCommand cm = new SqlCommand("tbSanPham_Get", cn);
           cm.CommandType = CommandType.StoredProcedure;
           cm.Parameters.AddWithValue("@Where", where);
           cm.Parameters.AddWithValue("@PageIndex", pageIndex);
           cm.Parameters.AddWithValue("@PageSize", pageSize);
           cm.Parameters.AddWithValue("@OrderBy", orderBy);
           cm.Parameters.AddWithValue("@OrderDirection", orderDirection);
           cm.Parameters.Add(new SqlParameter("@TotalRecords", DbType.Int32)).Direction = ParameterDirection.Output;

           SqlDataReader dr = cm.ExecuteReader();
           tbSanPhamCollection sanphamCollection = new tbSanPhamCollection();

           while (dr.Read())
           {
               tbSanPham th = new tbSanPham();
               th.masp = int.Parse(dr.GetValue(0).ToString());
               th.tinvantat = dr.GetValue(1).ToString();
               th.tensp = dr.GetValue(2).ToString();
               th.danhmuc = int.Parse(dr.GetValue(3).ToString());
               th.doanhnghiep = int.Parse(dr.GetValue(4).ToString());
               th.giagoc = float.Parse(dr.GetValue(5).ToString());
               th.giaban = float.Parse(dr.GetValue(6).ToString());
               th.soluongban = float.Parse(dr.GetValue(7).ToString());
               th.soluongtoida = float.Parse(dr.GetValue(8).ToString());
               th.damua = int.Parse(dr.GetValue(9).ToString());
               th.chietkhau = float.Parse(dr.GetValue(10).ToString());
               th.ngaysudung = DateTime .Parse(dr.GetValue(11).ToString());
               th.ngayhethan =DateTime .Parse(dr.GetValue(12).ToString());
               th.mota = dr.GetValue(13).ToString();
               th.tinhtrang = int.Parse(dr.GetValue(14).ToString());
               th.quydinh = dr.GetValue(15).ToString();
               th.thongtinchitiet = dr.GetValue(16).ToString();
               th.hinhchinh = dr.GetValue(17).ToString();
               sanphamCollection.Add(th);
           }
           dr.Close();
           totalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());
           return sanphamCollection;
       }
    }
}
